﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.IO.Ports;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using MasterDatabase;

namespace Job_Assignment
{
    public partial class Form1 : SQL_APPL
    {
        void InputFromPlannerList_Create_BT_Click(object sender, EventArgs e)
        {
            bool b;
            string sql_cmd;
            DataTable list_wst;
            string line_id, line_name, shift, wst, wst_name, part;
            DateTime date;
            TimeSpan from_time, to_time;
            int count;
            string mess;
            DataTable mainpart_tbl;

            // Hien thi chon ngay
            DateSelect_Dialog_Form selectDate_Dialog = new DateSelect_Dialog_Form(DateTime.Now);
            if (selectDate_Dialog.ShowDialog() == DialogResult.OK)
            {
                date = selectDate_Dialog.Select_Date;
                // lay du lieu ke hoach sx theo line cua ngay da chon
                sql_cmd = String.Format("SELECT * FROM [JOB_ASSIGNMENT_DB].[dbo].[P_001_InputFromPlanner] WHERE [Date] = '{0}' order by Priority", date.ToString("yyyy-MM-dd"));
                b = InputFromPlannerList_MasterDatabase.MasterDatabase_GridviewTBL.Load_DataBase(MasterDatabase_Connection_Str, sql_cmd);
                if (b == false)
                {
                    return;
                }

                count = InputFromPlannerList_MasterDatabase.MasterDatabase_GridviewTBL.Data_dtb.Rows.Count;
                if (count > 0)
                {
                    mess = "Plan for date:" + date.ToString("dd MMM yyyy") + "was existing\n";
                    mess += "Do you want to delete and create the new one?";

                    if (MessageBox.Show(mess, "Warning", MessageBoxButtons.YesNo) == DialogResult.No)
                    {
                        return;
                    }
                    // DeleteInputPlanfromPlaner(date);    
                    //TODO: Add Funtion delete existing data in P_001_InputFromPlanner by date          
                    string strConnectionStringSourceDel = "Server=VNMLTME-QNAM\\SQLEXPRESS;Database=JOB_ASSIGNMENT_DB;" +
                                                        "Integrated Security=True;" +
                                                        "Connection timeout=30";
                    SqlConnection conn = null;
                    conn = new SqlConnection(strConnectionStringSourceDel);
                    try
                    {
                        conn.Open();
                        SqlCommand myCommand1 = new SqlCommand("TRUNCATE TABLE P_001_InputFromPlanner", conn);
                        myCommand1.ExecuteNonQuery();
                        MessageBox.Show("P_001_InputFromPlanner table cleared");
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Could not delete data from P_001_InputFromPlanner. " + ex.Message);
                        conn.Close();
                    }
                    //End of delete function

                    
                }

                //TODO: Add Funtion copy data from kitting to P_001_InputFromPlanner
                //Prepare connection string for databases ----------------------------------------------------------------
                string strConnectionStringSource = "User id=read;Password=read;" +
                                                "Server=vnmsrv601.dl.net\\Siplace_2008R2EX;Database=DLVNDB;" +
                                                "Trusted_Connection=no;" +
                                                "Connection timeout=30";

                string strConnectionStringTarget = "Server=VNMLTME-QNAM\\SQLEXPRESS;Database=JOB_ASSIGNMENT_DB;" +
                                                "Integrated Security=True;" +
                                                "connection timeout=30";

                //Prepare temporary tables to store data ----------------------------------------------------------------
                SqlConnection conn1, conn2 = null;
                SqlDataAdapter daTABLENAME = null;
                DataTable dtTABLENAME = null;
                int intSourceTableCount = 0;

                //Connect to source databse DLVNDB ----------------------------------------------------------------------
                conn1 = new SqlConnection(strConnectionStringSource);
                try
                {
                    conn1.Open();
                    MessageBox.Show("Connected to DLVNDB.");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not connect to DLVNDB. " + ex.Message);
                }

                //Copy data from OpenPOPlanner table --------------------------------------------------------------------
                try
                {
                    daTABLENAME = new SqlDataAdapter("SELECT * FROM OpenPOPlanner " +
                                                    "WHERE DATEADD(day,0, DATEDIFF(day,0,ActiveDateTime))='" + date.Date.ToString() + "'", conn1);
                    dtTABLENAME = new DataTable();
                    daTABLENAME.Fill(dtTABLENAME);
                    intSourceTableCount = dtTABLENAME.Rows.Count;
                    conn1.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not copy from OpenPOPlanner table. " + ex.Message);
                    conn1.Close();
                }

                //Connect to target database JOB_ASSIGNMENT_DB ----------------------------------------------------------
                conn2 = new SqlConnection(strConnectionStringTarget);
                try
                {
                    conn2.Open();
                    MessageBox.Show("Connected to JOB_ASSIGNMENT_DB.");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not open JOB_ASSIGNMENT_DB. " + ex.Message);
                }

                // Copy PO data from OpenPOPlanner to InputFromPlanner table --------------------------------------------
                try
                {
                    int i;
                    MessageBox.Show("Planner PO table has " + intSourceTableCount.ToString() + " entries.");
                    for (i = 0; i < intSourceTableCount; i++)
                    {
                        SqlCommand myCommand = new SqlCommand("INSERT INTO P_001_InputFromPlanner (Date, PO, PartNumber, Qty, Priority) " +
                                                            "Values (" +
                                                            "'" + dtTABLENAME.Rows[i][8].ToString() + "'," +
                                                            "'" + dtTABLENAME.Rows[i][2].ToString() + "'," +
                                                            "'" + dtTABLENAME.Rows[i][3].ToString() + "'," +
                                                            "'" + dtTABLENAME.Rows[i][5].ToString() + "'," +
                                                            "'" + dtTABLENAME.Rows[i][4].ToString() + "')", conn2);
                        myCommand.ExecuteNonQuery();
                    }
                    MessageBox.Show(i.ToString() + " entri(es) copied.");
                }

                catch (Exception ex)
                {
                    MessageBox.Show("Could not copy data to InputFromPlanner table. " + ex.Message);
                }
                dtTABLENAME.Dispose();
                dtTABLENAME = null;
                conn1.Close();
                conn2.Close();

                InputFromPlannerList_MasterDatabase.MasterDatabase_GridviewTBL.Load_DataBase(MasterDatabase_Connection_Str, sql_cmd);
            }
            else
            {
            }
        }
    }
}